package com.jiepos.mpos.biz.channel.service;

import java.io.File;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.fileupload.disk.DiskFileItem;
import org.apache.commons.httpclient.NameValuePair;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.commons.CommonsMultipartFile;

import com.gexin.fastjson.JSON;
import com.jiepos.mpos.common.constant.DBConstant;
import com.jiepos.mpos.common.constant.DBConstant.Table;
import com.jiepos.mpos.common.constant.SystemConstant;
import com.jiepos.mpos.common.dao.HostLargeDao;
import com.jiepos.mpos.common.dao.HostTermDao;
import com.jiepos.mpos.common.entity.App_Custom_InfEntity;
import com.jiepos.mpos.common.entity.App_Host_TermEntity;
import com.jiepos.mpos.core.entity.Pagination;
import com.jiepos.mpos.core.service.BaseService;
import com.jiepos.mpos.core.util.DateUtil;
import com.jiepos.mpos.core.util.ExcelUtil;
import com.jiepos.mpos.core.util.HttpUtil;
import com.jiepos.mpos.core.util.IDFactoryUtil;
import com.jiepos.mpos.core.util.PropertiesUtil;
import com.jiepos.mpos.core.util.StringUtils;

/**
 * 
 * 
 * <p>标题: —— 主机终端表服务类</p>
 * <p>模块: 通用平台</p>
 * <p>版权: Copyright (c) 2017 江苏杰博实信息技术有限公司
 * @ProjectName:  [mpos] 
 * @Package com.jiepos.mpos.biz.channel.service 
 * @author 张学涛
 * @version 1.0
 * @since 2017年9月29日下午3:07:51
 * @history（历次修订内容、修订人、修订时间等）
 */
@Service
public class HostTermService extends BaseService {
	Properties properties = PropertiesUtil.getProperties("conf.properties");
	public static final Logger log = LoggerFactory.getLogger(HostTermService.class);
	
	@Autowired
	private HostTermDao hostTermDao;
	
	@Autowired
	private HostLargeDao hostLargeDao;	
	
	@Autowired
	private InfChannelService infChannelService;
	
	
	/**
	 * 查询主机终端列表json分页数据
	 * @param request
	 * @param pagination
	 * @param hostTerm
	 * @param ins_cd
	 * @param ins_cn_nm
	 * @param tablename
	 * @param channel_no
	 * @return
	 */
	public Map getHostTermList(HttpServletRequest request,Pagination pagination,App_Host_TermEntity hostTerm,
			String ins_cd,String ins_cn_nm,String tablename,String channel_no,String rec_crt_ts_start,String rec_crt_ts_end){
		StringBuffer sb = getInitSql("t1").append(",");
		sb.append("t1.host_term_uuid as uuid,t1.mchnt_cd,t1.term_id,t1.trf_mchnt_cd,t1.trf_term_id,t1.term_nm,NVL2 ( t2.ins_cd||t2.ins_cn_nm,t2.ins_cd||'-'||t2.ins_cn_nm, '') as insname,");
		sb.append("NVL2 ( t3.service_no||t3.service_name,t3.service_no||'-'||t3.service_name, '') as sername,NVL2 ( t4.param_no||t4.param_name,t4.param_no||'-'||t4.param_name, '') as purname,NVL2 ( t5.area_cd||t5.area_cn_nm,t5.area_cd||'-'||t5.area_cn_nm, '') as areaname,t1.acq_ins_cd,");
		sb.append("t1.master_key_idx,concat(rpad(substr( t1.master_key,1 , 6 ),length( t1.master_key )-4,'*') , substr( t1.master_key , length( t1.master_key)-3 , 4 ) ) as master_key,");		
		sb.append("NVL2 ( t6.channel_no||t6.chn_nm,t6.channel_no||'-'||t6.chn_nm, '')  as channel,to_char(t1.debit_minfee,'fm9999999990.00') as debit_minfee,to_char(t1.debit_rate,'fm9999999990.00') as debit_rate,to_char(t1.debit_maxfee,'fm9999999990.00') as debit_maxfee,to_char(t1.credit_minfee,'fm9999999990.00') as credit_minfee,");
		sb.append("to_char(t1.credit_rate,'fm9999999990.00') as credit_rate,to_char(t1.credit_maxfee,'fm9999999990.00') as credit_maxfee,NVL2 ( t7.code_val||t7.code_desc,t7.code_val||'-'||t7.code_desc, '')  as sign_st from ");
		sb.append(tablename.toUpperCase());
		sb.append(" t1 left join ");
		sb.append(Table.T_APP_INS_INF);
		sb.append(" t2 on t1.ins_uuid=t2.ins_uuid ");
		sb.append(" left join ");
		sb.append(Table.T_APP_SERVICE_INF);
		sb.append(" t3 on t1.service_uuid=t3.service_uuid ");
		sb.append(" left join ");
		sb.append(Table.T_APP_SERVICE_PARAM);
		sb.append(" t4 on t1.service_param_uuid=t4.service_param_uuid ");
		sb.append(" left join ");
		sb.append(Table.T_APP_AREA_CD);
		sb.append(" t5 on t1.area_cd=t5.area_cd ");
		sb.append(" left join ");
		sb.append(Table.T_APP_INF_CHANNEL);
		sb.append(" t6 on t6.channel_no='");
		sb.append(channel_no);
		sb.append("' left join ");
		sb.append(Table.T_APP_CODE_LIST);
		sb.append(" t7 on t1.sign_st=t7.code_val and t7.code_tp='SIGN_ST' ");
		sb.append(" where 1=1 ");
		List list = new ArrayList();
		if (!StringUtils.isBlank(hostTerm.getMchnt_cd())) {
			sb.append( " and t1.mchnt_cd like ? " );
			list.add("%" + hostTerm.getMchnt_cd() + "%");
		}
		if (!StringUtils.isBlank(hostTerm.getTerm_nm())) {
			sb.append( " and t1.term_nm like ? " );
			list.add("%" + hostTerm.getTerm_nm() + "%");
		}
		if (!StringUtils.isBlank(ins_cd)) {
			sb.append( " and t2.ins_cd like ? " );
			list.add("%" + ins_cd + "%");
		}
		if (!StringUtils.isBlank(ins_cn_nm)) {
			sb.append( " and t2.ins_cn_nm like ? " );
			list.add("%" + ins_cn_nm + "%");
		}
		if (!StringUtils.isBlank(hostTerm.getTrf_mchnt_cd())) {
			sb.append( " and t1.trf_mchnt_cd like ? " );
			list.add("%" + hostTerm.getTrf_mchnt_cd() + "%");
		}
		if (!StringUtils.isBlank(hostTerm.getSign_st())) {
			sb.append( " and t1.sign_st = ? " );
			list.add(hostTerm.getSign_st());
		}
		if (!StringUtils.isBlank(rec_crt_ts_start)) {
			Date date = DateUtil.strToDate(rec_crt_ts_start, DateUtil.FORMAT_DATE_1);
			sb.append("and to_char(t1.rec_crt_ts,'yyyyMMdd') >= ? ");
			list.add(DateUtil.dateToStr(date, "yyyyMMdd"));
		}else{ //无值默认今天
			sb.append("and to_char(t1.rec_crt_ts,'yyyyMMdd') >= ? ");
			list.add(DateUtil.dateToStr(new Date(), "yyyyMMdd"));			
		}
		if (!StringUtils.isBlank(rec_crt_ts_end)) {
			Date date = DateUtil.strToDate(rec_crt_ts_end, DateUtil.FORMAT_DATE_1);
			sb.append("and to_char(t1.rec_crt_ts,'yyyyMMdd') <= ? ");
			list.add(DateUtil.dateToStr(date, "yyyyMMdd"));
		}else{ //无值默认今天
			sb.append("and to_char(t1.rec_crt_ts,'yyyyMMdd') <= ? ");
			list.add(DateUtil.dateToStr(new Date(), "yyyyMMdd"));
		}
		pagination.queryString = sb.toString();
		//pagination = super.getInsAuthSql(request, pagination, "t2", null);
		pagination = this.getOrderSql(pagination, "t1");
		return hostTermDao.getPaginationMap(pagination, "t1", list);
	}
	/**
	 * 获取未进件会员列表
	 * @param request
	 * @param pagination
	 * @param customerInfo
	 * @param tablename
	 * @param channel_no
	 * @return
	 */
	public Map getUnreportedHostTermList(HttpServletRequest request,Pagination pagination,App_Custom_InfEntity customerInfo,
			String tablename,String channel_no){
		StringBuffer sb = getInitSql("t1").append(",");
		sb.append("t1.custom_uuid as uuid, t1.custom_no, ");
		sb.append("CONCAT (RPAD (SUBSTR (t1.phone, 1, 3),LENGTH (t1.phone) - 4,'*'),SUBSTR (t1.phone,LENGTH (t1.phone) - 3,4))||NVL2(t1.name, '-'||t1.name, '') as name, ");
		sb.append("(select code_val||'-'||code_desc from " + Table.T_APP_CODE_LIST
				+ " where code_val = t1.user_st and code_tp = '" + DBConstant.Code.USER_ST + "') as user_st, ");
		sb.append("(SELECT CONCAT (RPAD (SUBSTR (t2.phone, 1, 3),LENGTH (t2.phone) - 4,'*'),SUBSTR (t2.phone,LENGTH (t2.phone) - 3,4)) || NVL2(t2.name, '-'||t2.name, '') FROM " + Table.T_APP_CUSTOM_INF
				+ " t2 WHERE t2.custom_uuid = t1.sup_custom_uuid ) AS sup_custom_name,");
		sb.append("t3.ins_cd || NVL2(t3.ins_cn_nm, '-'||t3.ins_cn_nm, '') AS ins_name, ");
		sb.append("t4.area_cd || NVL2(t4.area_cn_nm, '-'||t4.area_cn_nm, '') AS area_cn_name, ");
		sb.append("(select code_val||'-'||code_desc from " + Table.T_APP_CODE_LIST
				+ " where code_val = t1.realname_auth_st and code_tp = '" + DBConstant.Code.REALNAME_AUTH_ST
				+ "') as realname_auth_st, ");
		sb.append("(select code_val||'-'||code_desc from " + Table.T_APP_CODE_LIST
				+ " where code_val = t1.realname_audit_st and code_tp = '" + DBConstant.Code.REALNAME_AUDIT_ST
				+ "') as realname_audit_st, ");
		sb.append("(select code_val||'-'||code_desc from " + Table.T_APP_CODE_LIST
				+ " where code_val = t1.vip_st and code_tp = '" + DBConstant.Code.VIP_ST + "') as vip_st, ");
		sb.append("to_char(t1.vip_auth_ts, 'yyyy-mm-dd hh24:mi:ss') as vip_auth_ts ");
		sb.append("from ");
		sb.append(Table.T_APP_CUSTOM_INF + " t1 ");
		sb.append("LEFT JOIN " + Table.T_APP_INS_INF + " t3 ON t1.ins_uuid = t3.ins_uuid ");
		sb.append("LEFT JOIN " + Table.T_APP_AREA_CD + " t4 ON t1.area_uuid = t4.area_uuid ");
		sb.append("where t1.custom_no not in (select t5.mchnt_cd from ");
		sb.append(tablename);
		sb.append(" t5 where t5.rec_st!='0') ");
		List list = new ArrayList();
		if (!StringUtils.isBlank(customerInfo.getPhone())) {
			sb.append(" and t1.phone like ? ");
			list.add("%" + customerInfo.getPhone() + "%");
		}
		if (!StringUtils.isBlank(customerInfo.getName())) {
			sb.append(" and t1.name like ? ");
			list.add("%" + customerInfo.getName() + "%");
		}
		if (!StringUtils.isBlank(customerInfo.getCustom_no())) {
			sb.append(" and t1.custom_no like ? ");
			list.add("%" +customerInfo.getCustom_no()+ "%");
		}
		pagination.queryString = sb.toString();
		pagination = super.getInsAuthSql(request, pagination, "t1", null);
		pagination = this.getOrderSql(pagination, "t1");
		return hostTermDao.getPaginationMap(pagination, "t1", list);
	}
	
	/**
	 * 保存主机终端
	 * @param hostTerm
	 * @param map
	 * @param tablename
	 */
	public void saveHostTerm(App_Host_TermEntity hostTerm,Map map,String tablename,String channel_no){	
		Map newHostTerm=null;
		List paramList = new ArrayList();	
		String masterkey=hostTerm.getMaster_key();
		if(StringUtils.isBlank(hostTerm.getHost_term_uuid())){						
			paramList.add(IDFactoryUtil.getUUID());
		}else{
			newHostTerm=hostTermDao.getEntityById(tablename, hostTerm.getHost_term_uuid());
			if(masterkey.contains("*")){
				masterkey=String.valueOf(newHostTerm.get("master_key"));
			}
		}
		paramList.add(hostTerm.getMchnt_cd());
		paramList.add(hostTerm.getTerm_id());
		paramList.add(hostTerm.getTerm_nm());
		paramList.add(hostTerm.getTrf_mchnt_cd());
		paramList.add(hostTerm.getTrf_term_id());
		//paramList.add(hostTerm.getIns_uuid());
		paramList.add(hostTerm.getService_uuid());
		paramList.add(hostTerm.getService_param_uuid());
		paramList.add(hostTerm.getArea_cd());
		//paramList.add(hostTerm.getAcq_ins_cd());
					
		paramList.add(hostTerm.getMaster_key_idx());							
		paramList.add(masterkey);
		paramList.add(hostTerm.getMac_key());
		paramList.add(hostTerm.getPin_key());
		paramList.add(hostTerm.getTrk_key());
		paramList.add(hostTerm.getDebit_minfee());
		paramList.add(hostTerm.getDebit_rate());
		paramList.add(hostTerm.getDebit_maxfee());
		paramList.add(hostTerm.getCredit_minfee());
		paramList.add(hostTerm.getCredit_rate());
		paramList.add(hostTerm.getCredit_maxfee());
		paramList.add(hostTerm.getSign_st());		
		if (!StringUtils.isBlank(hostTerm.getHost_term_uuid())) {																					
			paramList.add(new Timestamp(System.currentTimeMillis()));
			paramList.add(String.valueOf(map.get(SystemConstant.SESSION_LOGIN_USER)));
			paramList.add(SystemConstant.OPER_UPDATE);			
			paramList.add(newHostTerm.get("rec_id"));
			hostTermDao.updateEntity(paramList, tablename);
		}else{								
			paramList.add(SystemConstant.NORMAL_STATUS);
			paramList.add(new Timestamp(System.currentTimeMillis()));
			paramList.add(new Timestamp(System.currentTimeMillis()));
			paramList.add(String.valueOf(map.get(SystemConstant.SESSION_LOGIN_USER)));
			paramList.add(SystemConstant.OPER_ADD);			
			hostTermDao.saveEntity(paramList, tablename);
						
		}
		
	}
	
	/**
	 * 根据记录id查询实体对象
	 * @param host_term_uuid
	 * @param tablename
	 * @param channel_no
	 * @return
	 */
	public Map getHostTermDetail(String host_term_uuid,String tablename,String channel_no){
		if (StringUtils.isBlank(host_term_uuid) || host_term_uuid.equals("undefined")) {
			return new HashMap();
		}
		StringBuilder sb = new StringBuilder();
		sb.append("select t1.host_term_uuid,t1.mchnt_cd,t1.term_id,t1.trf_mchnt_cd,t1.trf_term_id,t1.term_nm,t1.ins_uuid,NVL2 ( t2.ins_cd||t2.ins_cn_nm,t2.ins_cd||'-'||t2.ins_cn_nm, '') as insname,");
		sb.append("t1.service_uuid,NVL2 ( t3.service_no||t3.service_name,t3.service_no||'-'||t3.service_name, '') as sername,t1.service_param_uuid,NVL2 ( t4.param_no||t4.param_name,t4.param_no||'-'||t4.param_name, '') as purname,t1.area_cd,NVL2 ( t5.area_cd||t5.area_cn_nm,t5.area_cd||'-'||t5.area_cn_nm, '') as areaname,t1.acq_ins_cd,");
		sb.append("t1.master_key_idx,concat(rpad(substr( t1.master_key,1 , 6 ),length( t1.master_key )-4,'*') , substr( t1.master_key , length( t1.master_key)-3 , 4 ) ) as master_key,");			
		sb.append("to_char(t1.debit_minfee,'fm9999999990.00') as debit_minfee,to_char(t1.debit_rate,'fm9999999990.00') as debit_rate,to_char(t1.debit_maxfee,'fm9999999990.00') as debit_maxfee,to_char(t1.credit_minfee,'fm9999999990.00') as credit_minfee,");
		sb.append("to_char(t1.credit_rate,'fm9999999990.00') as credit_rate,to_char(t1.credit_maxfee,'fm9999999990.00') as credit_maxfee,t1.sign_st,t1.rec_upd_usr_id from ");
		sb.append(tablename.toUpperCase());
		sb.append(" t1 left join ");
		sb.append(Table.T_APP_INS_INF);
		sb.append(" t2 on t1.ins_uuid=t2.ins_uuid ");
		sb.append(" left join ");
		sb.append(Table.T_APP_SERVICE_INF);
		sb.append(" t3 on t1.service_uuid=t3.service_uuid ");
		sb.append(" left join ");
		sb.append(Table.T_APP_SERVICE_PARAM);
		sb.append(" t4 on t1.service_param_uuid=t4.service_param_uuid ");
		sb.append(" left join ");
		sb.append(Table.T_APP_AREA_CD);
		sb.append(" t5 on t1.area_cd=t5.area_cd ");		
		sb.append(" where t1.rec_st!='");
		sb.append(SystemConstant.DEL_STATUS);
		sb.append("' and t1.host_term_uuid='");
		sb.append(host_term_uuid+"'");
		List<Map> list=hostTermDao.queryForList(sb.toString());
		if(list==null||list.size()==0){
			return new HashMap();
		}else{
			return list.get(0);
		}		
		
	}
	
	/**
	 * 删除
	 * @param host_term_uuids
	 * @param tablename
	 */
	public void deleteHostTermByIds(String host_term_uuids,String tablename){
		hostLargeDao.updateStatusById("host_term_uuid", host_term_uuids, tablename);
	}
	
	/**
	 * 校验一机一码
	 * @param hostTerm
	 * @param tablename
	 * @return
	 */
	public String checkHostTerm(App_Host_TermEntity hostTerm,String tablename){
		StringBuilder sb = new StringBuilder();
		sb.append("select host_term_uuid,mchnt_cd,term_id,term_nm,trf_mchnt_cd,trf_term_id,ins_uuid,service_uuid,service_param_uuid from ");		
		sb.append(tablename);
		sb.append(" where 1=1 ");
		List list = new ArrayList();
		if (!StringUtils.isBlank(hostTerm.getMchnt_cd())) {
			sb.append( " and mchnt_cd = ? " );
			list.add(hostTerm.getMchnt_cd());
		}	
		if (!StringUtils.isBlank(hostTerm.getTerm_id())) {
			sb.append( " and term_id = ? " );
			list.add(hostTerm.getTerm_id());
		}
		if (!StringUtils.isBlank(hostTerm.getHost_term_uuid())) {
			sb.append( " and host_term_uuid != ? " );
			list.add(hostTerm.getHost_term_uuid());
		}	
		List result=hostTermDao.queryForList(sb.toString(), list);
		if(result==null||result.size()==0){
			return SystemConstant.SUCCESS;
		}
		return SystemConstant.ERROR;
	}
	
	/**
	 * 校验商户代码和终端号是否存在
	 * @param hostTerm
	 * @param tablename
	 * @return
	 */
	public String checkImport(App_Host_TermEntity hostTerm,String tablename){
		StringBuilder sb = new StringBuilder();
		sb.append("select host_term_uuid,mchnt_cd,term_id,term_nm,trf_mchnt_cd,trf_term_id,ins_uuid,service_uuid,service_param_uuid from ");		
		sb.append(tablename);
		sb.append(" where 1=1 ");
		List list = new ArrayList();		
		sb.append( " and mchnt_cd = ? " );
		list.add(hostTerm.getMchnt_cd());			
		
		sb.append( " and term_id = ? " );
		list.add(hostTerm.getTerm_id());
					
		List result=hostTermDao.queryForList(sb.toString(), list);
		if(result==null||result.size()==0){
			return SystemConstant.SUCCESS;
		}
		return SystemConstant.ERROR;
	}
	
	/**
	 * 导入或批量修改
	 * @param tablename
	 * @param insertFile
	 * @param map
	 * @param flag
	 * @return
	 */
	public String importOrUpdateHostTerm(String tablename,MultipartFile insertFile,Map map,String flag){		
		ExcelUtil excelUtil = new ExcelUtil();
		CommonsMultipartFile cf = (CommonsMultipartFile)insertFile;  
		DiskFileItem fi = (DiskFileItem) cf.getFileItem(); 
		File file = fi.getStoreLocation();
		List<Map> hostTermList=excelUtil.readExcel(file);		
		Map lineMap=new HashMap();		
		List<String> errorLine=new ArrayList<String>();
		App_Host_TermEntity hostTerm=new App_Host_TermEntity();
		for(int i=1;i<hostTermList.size();i++){
			lineMap=hostTermList.get(i);
			hostTerm.setMchnt_cd(String.valueOf(lineMap.get("1")).trim());
			hostTerm.setTerm_id(String.valueOf(lineMap.get("2")).trim());
			String result=checkImport(hostTerm,tablename);
			if("1".equals(flag)){
				//导入				
				if(SystemConstant.ERROR.equals(result)){
					errorLine.add(String.valueOf(i));
				}else{
					if(!insertSelective(lineMap,tablename,map)){
						errorLine.add(String.valueOf(i));
					}
				}
			}else if("2".equals(flag)){
				//批量修改
				if(SystemConstant.SUCCESS.equals(result)){
					errorLine.add(String.valueOf(i));
				}else{
					if(!updateSelective(lineMap,tablename,map)){
						errorLine.add(String.valueOf(i));
					}
				}
			}
									
		}		
		if(errorLine.isEmpty()){			
			return SystemConstant.SUCCESS;
		}else{
			String errorLineStr =org.apache.commons.lang.StringUtils.join(errorLine.toArray(),",");			
			return errorLineStr;
		}	
		
	}
	
	/**
	 * 拼接导入sql
	 * @param lineMap
	 * @param tablename
	 * @param map
	 * @return
	 */
	public boolean insertSelective(Map lineMap,String tablename,Map map){
		try{
			StringBuilder sb = new StringBuilder();
			List paramList = new ArrayList();
			sb.append("insert into ").append(tablename).append(" (host_term_uuid,rec_st,rec_crt_ts,rec_upd_ts,rec_upd_usr_id,last_oper_in,");
			if(!StringUtils.isBlank(String.valueOf(lineMap.get("1")))){
				sb.append("mchnt_cd,");
			}
			if(!StringUtils.isBlank(String.valueOf(lineMap.get("2")))){
				sb.append("term_id,");
			}
			if(!StringUtils.isBlank(String.valueOf(lineMap.get("3")))){
				sb.append("trf_mchnt_cd,");
			}
			if(!StringUtils.isBlank(String.valueOf(lineMap.get("4")))){
				sb.append("trf_term_id,");
			}
			if(!StringUtils.isBlank(String.valueOf(lineMap.get("5")))){
				sb.append("term_nm,");
			}
			if(!StringUtils.isBlank(String.valueOf(lineMap.get("6")))){
				sb.append("master_key_idx,");
			}
			if(!StringUtils.isBlank(String.valueOf(lineMap.get("7")))){
				sb.append("master_key,");
			}
			if(!StringUtils.isBlank(String.valueOf(lineMap.get("8")))){
				sb.append("debit_minfee,");
			}
			if(!StringUtils.isBlank(String.valueOf(lineMap.get("9")))){
				sb.append("debit_rate,");
			}
			if(!StringUtils.isBlank(String.valueOf(lineMap.get("10")))){
				sb.append("debit_maxfee,");
			}
			if(!StringUtils.isBlank(String.valueOf(lineMap.get("11")))){
				sb.append("credit_minfee,");
			}
			if(!StringUtils.isBlank(String.valueOf(lineMap.get("12")))){
				sb.append("credit_rate,");
			}
			if(!StringUtils.isBlank(String.valueOf(lineMap.get("13")))){
				sb.append("credit_maxfee,");
			}
			if(!StringUtils.isBlank(String.valueOf(lineMap.get("14")))){
				sb.append("sign_st,");
			}
			sb.deleteCharAt(sb.length()-1);  
			sb.append(") VALUES (?,?,?,?,?,?,");	
			paramList.add(IDFactoryUtil.getUUID());
			paramList.add(SystemConstant.NORMAL_STATUS);
			paramList.add(new Timestamp(System.currentTimeMillis()));
			paramList.add(new Timestamp(System.currentTimeMillis()));
			paramList.add(String.valueOf(map.get(SystemConstant.SESSION_LOGIN_USER)));
			paramList.add(SystemConstant.OPER_ADD);			
			if(!StringUtils.isBlank(String.valueOf(lineMap.get("1")))){
				sb.append("?,");
				paramList.add(String.valueOf(lineMap.get("1")).trim());	
			}
			if(!StringUtils.isBlank(String.valueOf(lineMap.get("2")))){
				sb.append("?,");
				paramList.add(String.valueOf(lineMap.get("2")).trim());	
			}
			if(!StringUtils.isBlank(String.valueOf(lineMap.get("3")))){
				sb.append("?,");
				paramList.add(String.valueOf(lineMap.get("3")).trim());	
			}
			if(!StringUtils.isBlank(String.valueOf(lineMap.get("4")))){
				sb.append("?,");
				paramList.add(String.valueOf(lineMap.get("4")).trim());	
			}
			if(!StringUtils.isBlank(String.valueOf(lineMap.get("5")))){
				sb.append("?,");
				paramList.add(String.valueOf(lineMap.get("5")).trim());	
			}
			if(!StringUtils.isBlank(String.valueOf(lineMap.get("6")))){
				sb.append("?,");
				paramList.add(String.valueOf(lineMap.get("6")).trim());	
			}
			if(!StringUtils.isBlank(String.valueOf(lineMap.get("7")))){
				sb.append("?,");
				paramList.add(String.valueOf(lineMap.get("7")).trim());	
			}
			if(!StringUtils.isBlank(String.valueOf(lineMap.get("8")))){
				sb.append("?,");
				paramList.add(String.valueOf(lineMap.get("8")).trim());	
			}
			if(!StringUtils.isBlank(String.valueOf(lineMap.get("9")))){
				sb.append("?,");
				paramList.add(String.valueOf(lineMap.get("9")).trim());	
			}
			if(!StringUtils.isBlank(String.valueOf(lineMap.get("10")))){
				sb.append("?,");
				paramList.add(String.valueOf(lineMap.get("10")).trim());	
			}
			if(!StringUtils.isBlank(String.valueOf(lineMap.get("11")))){
				sb.append("?,");
				paramList.add(String.valueOf(lineMap.get("11")).trim());	
			}
			if(!StringUtils.isBlank(String.valueOf(lineMap.get("12")))){
				sb.append("?,");
				paramList.add(String.valueOf(lineMap.get("12")).trim());	
			}
			if(!StringUtils.isBlank(String.valueOf(lineMap.get("13")))){
				sb.append("?,");
				paramList.add(String.valueOf(lineMap.get("13")).trim());					
			}
			if(!StringUtils.isBlank(String.valueOf(lineMap.get("14")))){
				sb.append("?,");
				paramList.add(String.valueOf(lineMap.get("14")).trim());					
			}
			sb.deleteCharAt(sb.length()-1);  
			sb.append(")");
			hostTermDao.update(sb.toString(),paramList);
			return true;
		}catch(Exception e){
			e.printStackTrace();
			return false;
		}
	}
	
	/**
	 * 拼接批量修改sql
	 * @param lineMap
	 * @param tablename
	 * @param map
	 * @return
	 */
	public boolean updateSelective(Map lineMap,String tablename,Map map){
		try{
			StringBuilder sb = new StringBuilder();
			List paramList = new ArrayList();
			sb.append("update ").append(tablename).append(" set rec_upd_ts=?,rec_upd_usr_id=?,last_oper_in=?,");
			paramList.add(new Timestamp(System.currentTimeMillis()));
			paramList.add(String.valueOf(map.get(SystemConstant.SESSION_LOGIN_USER)));
			paramList.add(SystemConstant.OPER_UPDATE);	
			if(!StringUtils.isBlank(String.valueOf(lineMap.get("1")))){
				sb.append("mchnt_cd=?,");
				paramList.add(String.valueOf(lineMap.get("1")).trim());	
			}
			if(!StringUtils.isBlank(String.valueOf(lineMap.get("2")))){
				sb.append("term_id=?,");
				paramList.add(String.valueOf(lineMap.get("2")).trim());
			}
			if(!StringUtils.isBlank(String.valueOf(lineMap.get("3")))){
				sb.append("trf_mchnt_cd=?,");
				paramList.add(String.valueOf(lineMap.get("3")).trim());
			}
			if(!StringUtils.isBlank(String.valueOf(lineMap.get("4")))){
				sb.append("trf_term_id=?,");
				paramList.add(String.valueOf(lineMap.get("4")).trim());
			}
			if(!StringUtils.isBlank(String.valueOf(lineMap.get("5")))){
				sb.append("term_nm=?,");
				paramList.add(String.valueOf(lineMap.get("5")).trim());
			}
			if(!StringUtils.isBlank(String.valueOf(lineMap.get("6")))){
				sb.append("master_key_idx=?,");
				paramList.add(String.valueOf(lineMap.get("6")).trim());
			}
			if(!StringUtils.isBlank(String.valueOf(lineMap.get("7")))){
				sb.append("master_key=?,");
				paramList.add(String.valueOf(lineMap.get("7")).trim());
			}
			if(!StringUtils.isBlank(String.valueOf(lineMap.get("8")))){
				sb.append("debit_minfee=?,");
				paramList.add(String.valueOf(lineMap.get("8")).trim());
			}
			if(!StringUtils.isBlank(String.valueOf(lineMap.get("9")))){
				sb.append("debit_rate=?,");
				paramList.add(String.valueOf(lineMap.get("9")).trim());
			}
			if(!StringUtils.isBlank(String.valueOf(lineMap.get("10")))){
				sb.append("debit_maxfee=?,");
				paramList.add(String.valueOf(lineMap.get("10")).trim());
			}
			if(!StringUtils.isBlank(String.valueOf(lineMap.get("11")))){
				sb.append("credit_minfee=?,");
				paramList.add(String.valueOf(lineMap.get("11")).trim());
			}
			if(!StringUtils.isBlank(String.valueOf(lineMap.get("12")))){
				sb.append("credit_rate=?,");
				paramList.add(String.valueOf(lineMap.get("12")).trim());
			}
			if(!StringUtils.isBlank(String.valueOf(lineMap.get("13")))){
				sb.append("credit_maxfee=?,");
				paramList.add(String.valueOf(lineMap.get("13")).trim());
			}
			if(!StringUtils.isBlank(String.valueOf(lineMap.get("14")))){
				sb.append("sign_st=?,");
				paramList.add(String.valueOf(lineMap.get("14")).trim());
			}
			sb.deleteCharAt(sb.length()-1);
			sb.append(" where mchnt_cd=? and term_id=?");
			paramList.add(String.valueOf(lineMap.get("1")).trim());
			paramList.add(String.valueOf(lineMap.get("2")).trim());
			hostTermDao.update(sb.toString(),paramList);
			return true;
		}catch(Exception e){
			e.printStackTrace();
			return false;
		}
	}
	
	@SuppressWarnings("rawtypes")
	public String exportTemplate(String fileName, HttpServletRequest request, HttpServletResponse response){
		List<String> headList = new ArrayList<String>();
		headList.add("商户号");
		headList.add("终端号");
		headList.add("渠道方商户号");
		headList.add("渠道方终端号");
		headList.add("商户名称");
		headList.add("主秘钥索引");
		headList.add("主秘钥");
		headList.add("借记卡-最小手续费(元)");
		headList.add("借记卡-扣率(‱)");
		headList.add("借记卡-最大手续费(元)");
		headList.add("贷记卡-最小手续费(元)");
		headList.add("贷记卡-扣率(‱)");
		headList.add("贷记卡-最大手续费(元)");
		headList.add("签到标志（0,1）");
		return ExcelUtil.writeExcel(fileName, "一机一码上传模板", headList, new ArrayList());
	}
	
	/**
	 * 批量进件
	 * @param channelNo 渠道编号
	 * @param map
	 * @return
	 */
	public String intoBatch(String channelNo,Map map){
		NameValuePair[] data = { 
			new NameValuePair("channelNo",channelNo),
			new NameValuePair("operater",String.valueOf(map.get(SystemConstant.SESSION_LOGIN_USER))),
			new NameValuePair("intoType","into")
		};
		return reportToChannel(data);
	}
	
	/**
	 * 单个会员进件
	 * @param channelNo 渠道编号
	 * @param customerNo 会员编号
	 * @param map
	 * @return
	 */
	public String intoSingle(String channelNo, String customerNo, Map map){
		NameValuePair[] data = { 
			new NameValuePair("channelNo",channelNo),
			new NameValuePair("customerNo",customerNo),
			new NameValuePair("operater",String.valueOf(map.get(SystemConstant.SESSION_LOGIN_USER))),
			new NameValuePair("intoType","intoSingle")
		};
		return reportToChannel(data);
	}
	
	/**
	 * 批量修改渠道扣率
	 * @param channel_no
	 * @param ins_cd
	 * @param map
	 * @return
	 */
	public String modifyRateToChannel(String channel_no,String ins_cd,Map map){
		NameValuePair[] data = { 
			new NameValuePair("channelNo",channel_no),
			new NameValuePair("instNo",ins_cd),
			new NameValuePair("operater",String.valueOf(map.get(SystemConstant.SESSION_LOGIN_USER)) ),
			new NameValuePair("intoType","modify")
		};
		return reportToChannel(data);
	}
	
	/**
	 * 修改结算信息
	 * @param channel_no
	 * @param ins_cd
	 * @param map
	 * @return
	 */
	public String modifyAccountToChannel(String channel_no,String host_term_uuid,Map map){
		Map channel=infChannelService.getTableNameByParam(channel_no);	
		String tableName=String.valueOf(channel.get("chn_prmtb"));	
		Map hostTerm=hostTermDao.getEntityById(tableName, host_term_uuid);
		NameValuePair[] data = { 
			new NameValuePair("channelNo",channel_no),
			new NameValuePair("customerNo",String.valueOf(hostTerm.get("mchnt_cd"))),
			new NameValuePair("operater",String.valueOf(map.get(SystemConstant.SESSION_LOGIN_USER)) ),
			new NameValuePair("intoType","modifyAccount")
		};
		return reportToChannel(data);
	}
	
	/**
	 * 进件
	 * @param data
	 * @return
	 */
	private String reportToChannel(NameValuePair[] data){
		try{
			String url = properties.getProperty("mposinto.upload.url");
			String result = HttpUtil.getHttpClientSslData(url, data);
			log.info("进件请求返回信息[{}]",result);
			Map rspMap=JSON.parseObject(result);
			if(rspMap!=null&&!rspMap.isEmpty()){
				String respCode=String.valueOf(rspMap.get("respCode"));
				if(SystemConstant.RSPCODE_SUCCESS.equals(respCode)){
					return SystemConstant.SUCCESS;
				}
			}
			return SystemConstant.ERROR;
		}catch(Exception e){
			e.printStackTrace();
			return SystemConstant.ERROR;
		}
	}
}
